Eecient Roll-up and Drill-down Analysis in Relational Databases

Large collections of data, e.g. archives of POS (point-of-sale) in the consumer packaged goods as well as in prescription drugs, are typically iteratively classiied by a hierarchy 6] on the members of some attributes. Most of the fortune 500 businesses have found ways to consolidate the data archive into a relational database manager. One of the most common steps in \investigative data exploration" is the navigation of the hierarchy to glean valuable insights about the state of the business. The exploration answers \what if" questions, e.g., what-if the retailing of toys were discontinued in all stores and an investment made for building a retail organization for personal computers. To answer such questions, we are required to take the data associated with the lowest level of the hierarchy (e.g., sales) of individual items and sum it up to the ancestor at the highest level of the hierarchy, an operation referred to as roll-up. A human analyst, with the aid of tools, usually narrows down to one or more ancestors, then does analysis on the children. The later operation often involves obtaining sums for the children which is referred to as drill-down. Analysis would proceed recursively. The problem is to support eecient roll-up and drill-down on the data collection stored in the relational database management system. Two solutions are considered. In one, the hierarchy is stored as multiple map tables. In the second, members of the hierarchy are labeled in a manner that is sensitive to the hierarchy. Results of performance experiments comparing the two are given. Method using labeling has a factor of 3 advantage over the other. We also give the reasons why this may be so.